﻿using FAMailUtils.Data.MailBox.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FAMailUtils.Data.MailBox.DAO
{
    public class AccountDAO
    {
        DbHelper db = new DbHelper();
        public AccountDTO GetByAccountID(int AccountID, int ClientID)
        {
            string sql = "select * from tblMB_Accounts where AccountID = " + AccountID + " and ClientID = " + ClientID;
            AccountDTO result = new AccountDTO();
            result = (AccountDTO)db.GetObject(result, sql);
            return result;
        }

        public DataTable Search(string email, string accountName)
        {
            string sql = "select * from tblMB_Accounts where Email like '%" + email + "%' and AccountName like '%" + accountName + "%'";
            DataTable T = db.GetTable(sql);
            return T;
        }

        public bool SetMainDepartment(AccountDTO dto)
        {
            if (dto.IsMainDepartment == true)
            {
                string sql = "update tblMB_Accounts set IsMainDepartment = @IsMainDepartment from tblMB_Accounts where AccountID = @AccountID";
                object obj = db.ExecuteScalar(sql, new object[] { "AccountID", dto.AccountID, "IsMainDepartment", 0 });
                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
            else
            {
                string sql = "update tblMB_Accounts set IsMainDepartment = @IsMainDepartment from tblMB_Accounts where DepartmentID = @DepartmentID";
                object obj = db.ExecuteScalar(sql, new object[] { "DepartmentID", dto.DepartmentID, "IsMainDepartment", 0 });

                sql = "update tblMB_Accounts set IsMainDepartment = @IsMainDepartment from tblMB_Accounts where AccountID = @AccountID";
                obj = db.ExecuteScalar(sql, new object[] { "AccountID", dto.AccountID, "IsMainDepartment", 1 });

                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
        }

        public bool Block(int AccountID, int ClientID)
        {
            string sql = "select * from tblMB_Accounts where AccountID = " + AccountID + " and ClientID = " + ClientID;
            AccountDTO dto = new AccountDTO();
            dto = (AccountDTO)db.GetObject(dto, sql);

            if (dto.IsBlock == false)
            {
                string sql1 = "update tblMB_Accounts set IsBlock = 1, BlockDate = getdate() from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
                object obj = db.ExecuteScalar(sql1, new object[] { "AccountID", AccountID, "ClientID", ClientID });
                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
            else
            {
                string sql1 = "update tblMB_Accounts set IsBlock = 0 from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
                object obj = db.ExecuteScalar(sql1, new object[] { "AccountID", AccountID, "ClientID", ClientID });
                bool result = false;
                bool.TryParse(obj + "", out result);
                return result;
            }
        }

        public bool Delete(int AccountID, int ClientID)
        {
            string sql = "update tblMB_Accounts set isDelete = 1, deletedate = getdate() from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
            object obj = db.ExecuteScalar(sql, new object[] { "AccountID", AccountID, "ClientID", ClientID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public bool ResetPassword(int AccountID, int ClientID, string Password)
        {
            string sql = "update tblMB_Accounts set Password = @Password from tblMB_Accounts where AccountID=@AccountID and ClientID = @ClientID";
            object obj = db.ExecuteScalar(sql, new object[] { "AccountID", AccountID, "ClientID", ClientID, "Password", Password });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public DataTable GetAll(int clientID)
        {
            string sql = @"select a.* from tblMB_Accounts a where a.IsDelete = 0 and a.ClientID = @ClientID";
            DataTable T = db.GetTable(sql, new object[] { "ClientID", clientID });
            return T;
        }

        public int Create(AccountDTO dto)
        {
            string sql = @"insert into tblMB_Accounts 
                (AccountName, CreateDate, ClientID, IsBlock, IsMainDepartment, IsDelete, Phone, Password, [Function], Email, Address, DepartmentID, UserID) 
                values (@AccountName, @CreateDate, @ClientID, @IsBlock, @IsMainDepartment, @IsDelete, @Phone, @Password, @Function, @Email, @Address, @DepartmentID,@userID) select @@identity";
            object obj = db.ExecuteScalar(sql, new object[] { 
                "AccountName", dto.AccountName, 
                "CreateDate", dto.CreateDate,
                "ClientID", dto.ClientID, 
                "IsBlock", dto.IsBlock,
                "IsMainDepartment", dto.IsMainDepartment,
                "IsDelete", false,
                "Phone", dto.Phone,
                "Password", dto.Password,
                "Function", dto.Function, 
                "Email", dto.Email,
                "Address", dto.Address,
                "DepartmentID", dto.DepartmentID,
                "UserID", dto.UserID
            });
            int result = 0;
            int.TryParse(obj + "", out result);
            return result;
        }

        public bool Update(AccountDTO dto)
        {
            string sql = @"update tblMB_Accounts set AccountName = @AccountName, 
                        Phone = @Phone, 
                        Password = @Password, 
                        [Function] = @Function,
                        Email = @Email,
                        DepartmentID = @DepartmentID,
                        Address = @Address from tblMB_Accounts where AccountID = @AccountID";
                object obj = db.ExecuteScalar(sql, new object[] { "AccountName", dto.AccountName, 
                    "Phone", dto.Phone, 
                    "Password", dto.Password, 
                    "Function", dto.Function, 
                    "Email", dto.Email,
                    "Address", dto.Address,
                    "DepartmentID", dto.DepartmentID,
                    "AccountID", dto.AccountID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }
    }
}
